﻿#define DEBUG
//#undef DEBUG
using System;
using System.Activities;
using System.Activities.Presentation;
using System.Windows.Forms;
using System.Data;
using WFDataBaseProject.Helpers;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;
//using System.Drawing;
using System.ComponentModel;
using System.Reflection;
using System.Activities.Presentation.Metadata;
using System.Activities.Presentation.PropertyEditing;
using System.Activities.Presentation.Model;

namespace WFDataBaseProject
{
    /***The parameters***/

    /*
     1. Connection
     2. Connection type
     3. Query
     4. Source location
     5. Returned type
     */
    [Designer(typeof(UCExecuteSqlActivityDesigner))]
   // [ToolboxBitmap(typeof(ExecuteSqlActivity), "Images/execsql.jpg")]
    public class ExecuteSqlActivity:CodeActivity, WFDataBaseProject.IExecuteDBActivity
    {

        public ExecuteSqlActivity()
        {
            AttributeTableBuilder builder = new AttributeTableBuilder();
            builder.AddCustomAttributes(typeof(ExecuteSqlActivity), "FileName", new EditorAttribute(typeof(FilePickerEditor), typeof(DialogPropertyValueEditor)));
            builder.AddCustomAttributes(typeof(ExecuteSqlActivity), "ConnectionString", new EditorAttribute(typeof(ConnectionStringEditor), typeof(DialogPropertyValueEditor)));
            builder.AddCustomAttributes(typeof(ExecuteSqlActivity), "Query", new EditorAttribute(typeof(QueryEditor), typeof(DialogPropertyValueEditor)));
            MetadataStore.AddAttributeTable(builder.CreateTable());
        }
        
        
        #region arguments
        [RequiredArgument]
        [Description("The connection type sql server,oledb,odbc,oracle or other")]
        [Category("Inputs")]
        public InArgument<TypeOfConnection> ConnectionType { get; set; }
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The connection string to connect to the data base")]
        public InArgument<string> ConnectionString { get; set; }
        [Category("Inputs")]
        public InArgument<string> Query { get; set; }
        [Description("The file name form where to get the query")]
        [Category("Inputs")]
        public InArgument<string> FileName { get; set; }
        [RequiredArgument]
        [Description("The type of the source location whether it is inline,sql file or text file")]
        [Category("Inputs")]
        public InArgument<SqlSourceLocation> SourceLocation { get; set; }
        [RequiredArgument]
        [Description("The returned type whether it is a data table, a single record or just a none query execution")]
        [Category("Inputs")]
        public InArgument<TypeOfReturn> ReturnedType { get; set; }
        [Browsable(false)]
        public OutArgument<DataTable> ReturnedTable { get; set; }
        [Browsable(false)]
        public OutArgument<object> ReturnedScalar { get; set; }

        #endregion

        #region pivatefileds
        string filename;
        string query;
        TypeOfConnection connectiontype;
        string connectionstring;
        SqlSourceLocation sourcelocation;
        TypeOfReturn returnedtype;
        #endregion
        protected override void Execute(CodeActivityContext context)
            {
                connectiontype = ConnectionType.Get<TypeOfConnection>(context);
                connectionstring = ConnectionString.Get(context);
                sourcelocation = SourceLocation.Get(context);
                returnedtype = ReturnedType.Get(context);

                query = string.Empty;

                switch (sourcelocation)
                {
                    case SqlSourceLocation.InLine:
                        query = GetQueryFromSource(context, SqlSourceLocation.InLine);
                        break;
                    case SqlSourceLocation.TextFile:
                        query = GetQueryFromSource(context, SqlSourceLocation.TextFile);
                        break;
                    case SqlSourceLocation.SqlFile:
                        query = GetQueryFromSource(context, SqlSourceLocation.SqlFile);
                        break;
                    default:
                        break;
                }

             
                switch (connectiontype)
                {
                    case TypeOfConnection.OledbConnection:
                        {
                            #region oledb
                            OleDbConnection oledbconnection = new OleDbConnection(connectionstring);
                             using (oledbconnection)
                             {
                                
                                 try
                                 {
                                     oledbconnection.Open();
                                     OleDbCommand oledbcommmand = new OleDbCommand(query, oledbconnection);

                                     switch (returnedtype)
                                     {
                                         case TypeOfReturn.Request:
                                             OleDbDataReader reader = oledbcommmand.ExecuteReader();
                                             DataTable table = new DataTable(Guid.NewGuid().ToString());
                                             table.Load(reader);
                                             ReturnedTable.Set(context,table);
                                             break;
                                         case TypeOfReturn.Scalar:
                                             object retObj = oledbcommmand.ExecuteScalar();
                                             ReturnedScalar.Set(context,retObj);
                                             break;
                                         case TypeOfReturn.None:
                                             oledbcommmand.ExecuteNonQuery();
#if DEBUG
                            Debug.Print("Query is executed");
#endif
                                             break;
                                         default:
                                             break;

                                     }
                                 }
                                 catch (OleDbException caught)
                                 {
#if DEBUG
                                    Debug.Print(caught.Message);
#endif  
                                 }
                                
                             }

                            #endregion      
                            break;  
                        }

                    case TypeOfConnection.SqlServerConnection:
                        {
                            #region sql
                            SqlConnection sqlconnection = new SqlConnection(connectionstring);
                            using (sqlconnection)
                            {

                                try
                                {
                                    sqlconnection.Open();
                                    SqlCommand sqlcommmand = new SqlCommand(query, sqlconnection);

                                    switch (returnedtype)
                                    {
                                        case TypeOfReturn.Request:
                                            SqlDataReader reader = sqlcommmand.ExecuteReader();
                                            DataTable table = new DataTable(Guid.NewGuid().ToString());
                                            table.Load(reader);
                                            ReturnedTable.Set(context, table);
                                            break;
                                        case TypeOfReturn.Scalar:
                                            object retObj = sqlcommmand.ExecuteScalar();
                                            ReturnedScalar.Set(context, retObj);
                                            break;
                                        case TypeOfReturn.None:
                                            sqlcommmand.ExecuteNonQuery();
#if DEBUG
                                            Debug.Print("Query is executed");
#endif
                                            break;
                                        default:
                                            break;

                                    }
                                }
                                catch (SqlException caught)
                                {
#if DEBUG
                                    Debug.Print(caught.Message);
#endif
                                }

                            }

                            #endregion                       
                            break;
                        }
                    case TypeOfConnection.ODBCConnection:
                        {
                            #region odbc
                            OdbcConnection odbcconnection = new OdbcConnection(connectionstring);
                            using (odbcconnection)
                            {

                                try
                                {
                                    odbcconnection.Open();
                                    OdbcCommand odbccommmand = new OdbcCommand(query, odbcconnection);

                                    switch (returnedtype)
                                    {
                                        case TypeOfReturn.Request:
                                            OdbcDataReader reader = odbccommmand.ExecuteReader();
                                            DataTable table = new DataTable(Guid.NewGuid().ToString());
                                            table.Load(reader);
                                            ReturnedTable.Set(context, table);
                                            break;
                                        case TypeOfReturn.Scalar:
                                            object retObj = odbccommmand.ExecuteScalar();
                                            ReturnedScalar.Set(context, retObj);
                                            break;
                                        case TypeOfReturn.None:
                                            odbccommmand.ExecuteNonQuery();
#if DEBUG
                                            Debug.Print("Query is executed");
#endif
                                            break;
                                        default:
                                            break;

                                    }
                                }
                                catch (SqlException caught)
                                {
#if DEBUG
                                    Debug.Print(caught.Message);
#endif
                                }

                            }

                            #endregion
                            break;
                        }
                    case TypeOfConnection.OtherConnection:
                        break;
                    default:
                        break;
                }
                
            }     
            string GetQueryFromSource(CodeActivityContext context,SqlSourceLocation sourcelocation)
            {
                string query = string.Empty;

                switch (sourcelocation)
                {
                    case SqlSourceLocation.InLine:
                        {
                            query = Query.Get<string>(context);
                            break;
                        }

                    case SqlSourceLocation.TextFile:
                        {
                            GetFileName(context,FileFilter.Txt);
                            try
                            {
                                StreamReader reader = new StreamReader(filename);
                                Query.Set(context, reader.ReadToEnd());
                                query = Query.Get<string>(context);
                            }
                            catch (ApplicationException caught)
                            {
                                MessageBox.Show(caught.Message);
                            }
                            
                            break; 
                        }
                    case SqlSourceLocation.SqlFile:
                        {
                            GetFileName(context,FileFilter.Sql);
                            try
                            {
                                StreamReader reader = new StreamReader(filename);
                                Query.Set(context, reader.ReadToEnd());
                                query = Query.Get<string>(context);
                            }
                            catch (ApplicationException caught)
                            {
                                MessageBox.Show(caught.Message);
                            }
                            break;
                        }
                    default:
                        throw new ApplicationException("No source query found");
                }
                return query;
            }
            void GetFileName(ActivityContext context,FileFilter filefilter)
            {
                OpenFileDialog fileDialog;
                filename = FileName.Get<string>(context);
                switch (filefilter)
                {
                    case FileFilter.Txt:
                        {
                            fileDialog = new OpenFileDialog();
                            fileDialog.Filter = "(*.txt)|*.txt";
                            if (fileDialog.ShowDialog()== DialogResult.OK)
                            {
                                filename = fileDialog.FileName;
                            }

                            break;
                        }
                    case FileFilter.Sql:
                        {
                            fileDialog = new OpenFileDialog();
                            fileDialog.Filter = "(*.sql)|*.sql";
                            if (fileDialog.ShowDialog() == DialogResult.OK)
                            {
                                filename = fileDialog.FileName;
                            }
                            break;
                        }
                    default:
                        {
                            throw new ApplicationException("The type of the input should be chosen");
                        } 
                }

            }
            
        
    }
}
